﻿    CREATE OR REPLACE FUNCTION update_student(
        p_id                character varying(36),
        p_full_name         character varying(128),
        p_display_name      character varying(128),
        p_nationality_id    character varying(36)
    )
    RETURNS 
    VARCHAR(36)
    AS
    $$
        /***************************************************************************************************
    **  Function:	public.update_student
    **  Author:		Zhixian Ong
    **
    **  Description:
    **    
    **      Update student with specified id.
    **
    **  Parameters (1):
    **
    **      id  VARCHAR(36) - id of student to remove
    **
    **  Return:
    **
    **      VARCHAR(36)
    **
    **  Sample call:
    **
    **      Add 'Zhixian' as a student.
            
            SELECT * FROM student WHERE display_name = 'Zhixian';
            SELECT add_student('Ong Zhi Xian', 'Zhixian', 'S0000000Z')
            
            SELECT update_student('1016', 'Ong Zhi Xian2', 'Zhixian2', 'S0000000Z2');
            SELECT * FROM student WHERE display_name = 'Zhixian2';
            
    **
    **  Modification History:
    **
    **      20140102  Zhixian Ong     - Initial prototype
    ** 
    **  Status:
    **
    **      Dev
    ** 
    ***************************************************************************************************/
    DECLARE
        l_id VARCHAR(36);
        
    BEGIN
            
        --------------------------------------------------------------------------------
        -- Return result
        --------------------------------------------------------------------------------
        -- SELECT * FROM student;
        UPDATE  student 
        SET     full_name       = p_full_name,
                display_name    = p_display_name,
                nationality_id  = p_nationality_id
        WHERE   id = p_id
        RETURNING id INTO l_id;
        
        RETURN l_id;
        
    END;
    $$ LANGUAGE plpgsql;

    